package com.ctvit.syn.service;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.util.*;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.apache.commons.lang3.StringUtils;
import com.ctvit.framework.service.BaseService;

public class SyncDataBaseService extends BaseService {

	private static final long serialVersionUID = 1L;
	protected static final int BATCH_SIZE = 500;

	private DataSource dataSource_remote;
	private DataSource dataSource;
	private DataSource dataSource_notice;
	private Connection connect_remote;
	private Connection connect_local;
	private Connection connect_notice;

	public Connection getConnect_local() {
		try {
			connect_local = dataSource.getConnection();
			connect_local.setAutoCommit(false);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return connect_local;
	}
	
	public Connection getConnect_notice() {
		try {
			connect_notice = dataSource_notice.getConnection();
			connect_notice.setAutoCommit(false);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return connect_notice;
	}

	public Connection getConnect_remote() {
		try {
			connect_remote = dataSource_remote.getConnection();
			connect_remote.setAutoCommit(false);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return connect_remote;
	}

	public String getRemoteSysId() {
		try {
			InputStream is = getClass().getClassLoader().getResourceAsStream("config/database.properties");
			Properties dbProps = new Properties();
			dbProps.load(is);
			String sysid = (String) dbProps.get("remote.sysid");
			if (!StringUtils.isEmpty(sysid)) {
				return sysid;
			} else {
//				System.out.println("请检查数据库配置文件是否正确");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}


	void updateTime(String src,String dest,String tableName, Date date, String type,String pkValues) throws SQLException {
		PreparedStatement pstmt = null;
		Connection conn=null;
		ResultSet rs=null;
		try {
			conn = getConnect_local();//不论源、目标数据库，最后同步时间记录在local
			String sql = "select CREATE_TIMESTAMP,MODIFY_TIMESTAMP from QRTZ_SYNC_TIMESTAMP where name = '" + tableName.toUpperCase() + "' and SOURCESYS='"+src.toUpperCase()+"' and DESTSYS='"+dest.toUpperCase()+"'";;
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			Date createTime = null;
			Date modifyTime = null;
			while (rs.next()) {
				createTime = rs.getTimestamp("CREATE_TIMESTAMP");
				modifyTime = rs.getTimestamp("MODIFY_TIMESTAMP");
			}

			if ("insert".equals(type)) {
				createTime = date;
			} else if ("update".equals(type)) {
				modifyTime = date;
			} else if ("all".equalsIgnoreCase(type)) {
				createTime = date;
				modifyTime = date;
			}
			if (!"delete".equals(type)) {
				String updateSql = "update QRTZ_SYNC_TIMESTAMP set CREATE_TIMESTAMP = ?,MODIFY_TIMESTAMP = ? where name = ? and SOURCESYS=? and destsys=?";
				pstmt = conn.prepareStatement(updateSql);
				if (createTime == null) {
					pstmt.setObject(1, createTime);
				} else {
					pstmt.setTimestamp(1, new Timestamp(createTime.getTime()));
				}
				if (modifyTime == null) {
					pstmt.setObject(2, modifyTime);
				} else {
					pstmt.setTimestamp(2, new Timestamp(modifyTime.getTime()));
				}
	
				pstmt.setString(3, tableName.toUpperCase());
				pstmt.setString(4, src.toUpperCase());
				pstmt.setString(5, dest.toUpperCase());
				int ret = pstmt.executeUpdate();
	
				if (ret < 1) {
					String insertSql = "insert into QRTZ_SYNC_TIMESTAMP (SOURCESYS,destsys,NAME,CREATE_TIMESTAMP,MODIFY_TIMESTAMP) values(?,?,?,?,?)";
					pstmt = conn.prepareStatement(insertSql);
					pstmt.setString(1, src.toUpperCase());
					pstmt.setString(2, dest.toUpperCase());
					pstmt.setString(3, tableName.toUpperCase());
					if (createTime == null) {
						pstmt.setObject(4, createTime);
					} else {
						pstmt.setTimestamp(4, new Timestamp(createTime.getTime()));
					}
					if (modifyTime == null) {
						pstmt.setObject(5, modifyTime);
					} else {
						pstmt.setTimestamp(5, new Timestamp(modifyTime.getTime()));
					}
					pstmt.execute();
				}
			}else{
				String updateSql = "update QRTZ_SYNC_TIMESTAMP set PKVALUE=REPLACE(PKVALUE,'"+pkValues+"','') where name = ? and SOURCESYS=? and destsys=?";
				pstmt = conn.prepareStatement(updateSql);
				pstmt.setString(1, src.toUpperCase());
				pstmt.setString(2, dest.toUpperCase());
				pstmt.setString(3, tableName.toUpperCase());
				int ret = pstmt.executeUpdate();
			}
			conn.commit();
		} catch (SQLException e) {
			throw e;
		}finally {
			JDBCUtil.closeResultSet(rs);
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(conn);
		}
	}

	public Date getLastSynDate(String src,String dest,String tableName, String timeType) throws SQLException, ParseException {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			conn = getConnect_local();
			String sql = "select date_add(CREATE_TIMESTAMP,interval -2 second) as CREATE_TIMESTAMP,date_add(MODIFY_TIMESTAMP,interval -2 second) as MODIFY_TIMESTAMP from QRTZ_SYNC_TIMESTAMP where name = '"
					+ tableName.toUpperCase() + "' and SOURCESYS='"+src.toUpperCase()+"' and destsys='"+dest.toUpperCase()+"'";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			Date createTime = null;
			Date modifyTime = null;
			while (rs.next()) {
				createTime = rs.getTimestamp("CREATE_TIMESTAMP");
				modifyTime = rs.getTimestamp("MODIFY_TIMESTAMP");
			}
			if ("insert".equals(timeType)) {
				if (createTime != null) {
					return createTime;
				} else {
					return new Date(0);
				}
			}
			if ("update".equals(timeType)) {
				if (modifyTime != null) {
					return modifyTime;
				} else {
					if (createTime != null) {
						return createTime;// 从创建同步的开始进行更新
					} else {
						return new Date(0);
					}
				}
			}
			return null;
		} catch (SQLException e) {
			throw e;
		} finally {
			JDBCUtil.closeResultSet(rs);
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(conn);
		}
	}

	boolean recordExists(String tablename, String pkField, Object value,String dest) throws SQLException {
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		try {
			if (dest.equals("local")){
				conn = getConnect_local();
			}else if (dest.equals("remote")){
				conn = getConnect_remote();
			}else if (dest.equals("notice")){
				conn = getConnect_notice();
			}
			String sql = "Select * from " + tablename + "  where " + pkField + "=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setObject(1, value);
			rs = pstmt.executeQuery();
			boolean exist = false;
			while (rs.next()) {
				exist = true;
				break;
			}
			return exist;
		} catch (SQLException e) {
			JDBCUtil.rollback(conn);
			throw e;
		} finally {
			JDBCUtil.closeResultSet(rs);
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(conn);
		}
	}

	boolean recordExists(String tablename, String pkField[], ArrayList valueList,String dest) throws SQLException {
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		try {
			if (dest.equals("local")){
				conn = getConnect_local();
			}else if (dest.equals("remote")){
				conn = getConnect_remote();
			}else if (dest.equals("notice")){
				conn = getConnect_notice();
			}
			int lastValueIndex;
			StringBuffer tosql = new StringBuffer("Select * from ");
			tosql.append(tablename);
			tosql.append("  where ");
			for (int i = 0; i < pkField.length; i++) {
				lastValueIndex = pkField.length - 1;
				if (i == lastValueIndex) {
					tosql.append(pkField[i] + " = ?");
				} else {
					tosql.append(pkField[i] + " = ? and ");
				}
			}
			pstmt = conn.prepareStatement(tosql.toString());
			for (int i = 0; i < valueList.size(); i++) {
				pstmt.setObject(i + 1, valueList.get(i));
			}
			rs = pstmt.executeQuery();
			boolean exist = false;
			while (rs.next()) {
				exist = true;
				break;
			}
			return exist;
		} catch (SQLException e) {
			JDBCUtil.rollback(conn);
			throw e;
		} finally {
			JDBCUtil.closeResultSet(rs);
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(conn);
		}
	}

	boolean existData(String tablename, String fieldname, Object value) throws SQLException {
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		try {
			conn = getConnect_remote();
			String sql = "Select 123 from " + tablename + "  where " + fieldname + "=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setObject(1, value);
			rs = pstmt.executeQuery();
			boolean exist = false;
			while (rs.next()) {
				exist = true;
				break;
			}
			return exist;
		} catch (SQLException e) {
			JDBCUtil.rollback(conn);
			throw e;
		} finally {
			JDBCUtil.closeResultSet(rs);
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(conn);
		}
	}

	public DataSource getDataSource_remote() {
		return dataSource_remote;
	}
//	@Resource(name = "remoteDataSource")
	public void setDataSource_remote(DataSource remoteDataSource) {
		dataSource_remote = remoteDataSource;
	}

	public DataSource getDataSource_notice() {
		return dataSource_notice;
	}
//	@Resource(name = "noticeDataSource")
	public void setDataSource_notice(DataSource noticeDataSource) {
		this.dataSource_notice = noticeDataSource;
	}

	public DataSource getDataSource() {
		return dataSource;
	}
//    @Resource(name = "dataSource")
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
}
